﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using PPI.Entities;
using PPI.Mobile.Logic;
using VMind.Global;
using System.Data;


namespace PPI.Client.Logic
{
    public static class LVendor_Visit_XMissionStructures_Pole
    {
        public static Int32 Save(EVendor_Visit_XMissionStructures_Pole pole)
        {
            return
                WebConsumer.WVendor_Visit_XMissionStructures_Pole.Save(pole.Id, pole.Asset.Id, pole.TopType.Id,
                                                                       pole.Species.Id, pole.Treatment.Id, pole.Year,
                                                                       pole.Class.Id, pole.Latitude, pole.Longitude,
                                                                       pole.Altitude, pole.SatelliteCount,
                                                                       pole.SatelliteTime, pole.AID, pole.LVisit,
                                                                       pole.Number, pole.Length, pole.LVisitor,
                                                                       pole.SolutionAvailable);
        }

        public static void Delete(EVendor_Visit_XMissionStructures_Pole p)
        {
            WebConsumer.WVendor_Visit_XMissionStructures_Pole.Delete(p.Id);
        }

        #region Mobile

        public static void UpdateUploadedData(EVendor_Visit_XMissionStructures_Pole pole, string connectionString)
        {
            StringBuilder _query = new StringBuilder();

            _query.AppendLine("Update tb_Vendor_Visit_XStructureMappingPoles set PoleServerID =" + pole.ServerID + ",UploadedDate = " +
                              DateTime.Now.ToString("yyyy-MM-dd") + " where PoleId =" + pole.Id + " ");

            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
            _query = new StringBuilder();
            _query.AppendLine("update tb_Vendor_Visit_Images set PoleServerID =" + pole.ServerID +
                              " where PoleFk =" + pole.Id + "   ");
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
        }

        public static void SaveMobile(EVendor_Visit_XMissionStructures_Pole pole, string connectionString)
        {
            Int32 returnId = pole.Id;
            try
            {
                if (pole.Id != Int32.MinValue)
                    UpdateMobile(pole, connectionString);
                else
                {
                    pole.Id = InsertMobile(pole, connectionString);

                }
                

            }
            catch (Exception ex)
            {
                
                throw;
            }
        }

        public static void UpdateMobile(EVendor_Visit_XMissionStructures_Pole pole, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();

            #region Query

            _Query.AppendLine("Update tb_Vendor_Visit_XStructureMappingPoles SET ");
            _Query.AppendLine(" TopTypeFk = @TopTypeFk, ");
            _Query.AppendLine(" SpeciesFk = @SpeciesFk, ");
            _Query.AppendLine(" TreatmentFk = @TreatmentFk, ");
            _Query.AppendLine(" Year = @Year, ");
            _Query.AppendLine(" ClassFk = @ClassFk, ");
            _Query.AppendLine(" Latitude = @Latitude, ");
            _Query.AppendLine(" Longitude = @Longitude, ");
            _Query.AppendLine(" Altitude = @Altitude, ");
            _Query.AppendLine(" SatelliteCount = @SatelliteCount, ");
            _Query.AppendLine(" SatelliteTime = @SatelliteTime, ");
            _Query.AppendLine(" LVisit = @LVisit, ");
            _Query.AppendLine(" Length = @Length, ");
            _Query.AppendLine(" LVisitor = @LVisitor, ");
            _Query.AppendLine(" Number = @Number ");
            _Query.AppendLine(" Where  PoleId = @PoleId ");


            #endregion

            #region Parameters

            List<SqlParameter> listParameter = new List<SqlParameter>();

            SqlParameter tmpParameter = null;

            if (pole.Latitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, pole.Latitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Longitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, pole.Longitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Altitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, pole.Altitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (pole.SatelliteCount > 0)
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, pole.SatelliteCount);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (pole.SatelliteTime != DateTime.MinValue)
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, false, pole.SatelliteTime);
            else
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);



            if (pole.Species.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@SpeciesFk", ParameterDirection.Input, false, pole.Species.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SpeciesFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.TopType.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, false, pole.TopType.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Treatment.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, pole.Treatment.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Year != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, pole.Year);
            else
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Class.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, pole.Class.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.LVisit != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, false, pole.LVisit);
            else
                tmpParameter = DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Length != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, false, pole.Length);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (!String.IsNullOrEmpty(pole.LVisitor))
                tmpParameter = DBHelper.CreateNVarCharParameter("@LVisitor", 255, ParameterDirection.Input, false, pole.LVisitor);
            else
                tmpParameter = DBHelper.CreateNVarCharParameter("@LVisitor",255, ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (!String.IsNullOrEmpty(pole.Number))
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number", 255, ParameterDirection.Input, false, pole.Number);
            else
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number", 255, ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            tmpParameter = DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id);
            listParameter.Add(tmpParameter);

            #endregion

            
            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameter.ToArray());


        }

        public static Int32 InsertMobile(EVendor_Visit_XMissionStructures_Pole pole, string connectionString)
        {

            StringBuilder _Query = new StringBuilder();

            #region Query

            _Query.AppendLine("Select DataId from tb_Vendor_Visit_XStructureMappingData where VisitFk = "+pole.Asset.Id+" ");
            

            #endregion

            Int32 dataFk = (Int32)LMobileDatabase.ExecuteScalar(_Query.ToString(), connectionString);

            _Query = new StringBuilder();

            #region Query

            _Query.AppendLine("Insert into tb_Vendor_Visit_XStructureMappingPoles (");
            _Query.AppendLine("     SolutionAvailable,DataFk,TopTypeFk,SpeciesFk,TreatmentFk,Year,ClassFK,Latitude,Longitude,Altitude,SatelliteCount, ");
            _Query.AppendLine("     SatelliteTime,Lvisit,Length,LVisitor,Number");
            _Query.AppendLine(") values(");
            _Query.AppendLine("     @SolutionAvailable,@DataFk,@TopTypeFk,@SpeciesFk,@TreatmentFk,@Year,@ClassFk,@Latitude,@Longitude, ");
            _Query.AppendLine("     @Altitude, @SatelliteCount, @SatelliteTime,@LVisit,@Length,@LVisitor,@Number");
            _Query.AppendLine(")");

            #endregion

            #region Parameters

            List<SqlParameter> listParameter = new List<SqlParameter>();

            SqlParameter tmpParameter = null;

            if (pole.Latitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, pole.Latitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Longitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, pole.Longitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Altitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, pole.Altitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (pole.SatelliteCount > 0)
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, pole.SatelliteCount);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (pole.SatelliteTime != DateTime.MinValue)
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, false, pole.SatelliteTime);
            else
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);



            if (pole.Species.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@SpeciesFk", ParameterDirection.Input, false, pole.Species.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SpeciesFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.TopType.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, false, pole.TopType.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Treatment.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, pole.Treatment.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Year != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, pole.Year);
            else
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Class.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, pole.Class.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.LVisit != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, false, pole.LVisit);
            else
                tmpParameter = DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Length != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, false, pole.Length);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (!String.IsNullOrEmpty(pole.LVisitor))
                tmpParameter = DBHelper.CreateNVarCharParameter("@LVisitor", 255, ParameterDirection.Input, false, pole.LVisitor);
            else
                tmpParameter = DBHelper.CreateNVarCharParameter("@LVisitor", 255, ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (!String.IsNullOrEmpty(pole.Number))
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number", 255, ParameterDirection.Input, false, pole.Number);
            else
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number", 255, ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            listParameter.Add(DBHelper.CreateIntParameter("@DataFk", ParameterDirection.Input, false, dataFk));
            listParameter.Add(DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, pole.SolutionAvailable));
            

            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameter.ToArray());

            _Query = new StringBuilder("Select max(PoleId)  from tb_Vendor_Visit_XStructureMappingPoles ");
            return (Int32)LMobileDatabase.ExecuteScalar(_Query.ToString(), connectionString);

        }

        public static void InsertMobileProject(EVendor_Visit_XMissionStructures_Pole pole, string connectionString)
        {

            StringBuilder _Query = new StringBuilder();
            #region Query

            _Query.AppendLine("Insert into tb_Vendor_Visit_XStructureMappingPoles (");
            _Query.AppendLine("     poleId,SolutionAvailable,visitFk,TopTypeFk,SpeciesFk,TreatmentFk,Year,ClassFK,Latitude,Longitude,Altitude,SatelliteCount, ");
            _Query.AppendLine("     SatelliteTime,Lvisit,Length,LVisitor,Number");
            _Query.AppendLine(") values(");
            _Query.AppendLine("     @poleId,@SolutionAvailable,@visitFk,@TopTypeFk,@SpeciesFk,@TreatmentFk,@Year,@ClassFk,@Latitude,@Longitude, ");
            _Query.AppendLine("     @Altitude, @SatelliteCount, @SatelliteTime,@LVisit,@Length,@LVisitor,@Number");
            _Query.AppendLine(")");

            #endregion

            #region Parameters

            List<SqlParameter> listParameter = new List<SqlParameter>();

            SqlParameter tmpParameter = null;

            if (pole.Latitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, pole.Latitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Longitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, pole.Longitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Altitude != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, pole.Altitude);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (pole.SatelliteCount > 0)
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, pole.SatelliteCount);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            if (pole.SatelliteTime != DateTime.MinValue)
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, false, pole.SatelliteTime);
            else
                tmpParameter = DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);



            if (pole.Species.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@SpeciesFk", ParameterDirection.Input, false, pole.Species.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SpeciesFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.TopType.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, false, pole.TopType.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Treatment.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, pole.Treatment.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Year != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, pole.Year);
            else
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Class.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, pole.Class.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.LVisit != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, false, pole.LVisit);
            else
                tmpParameter = DBHelper.CreateIntParameter("@LVisit", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (pole.Length != double.MinValue)
                tmpParameter = DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, false, pole.Length);
            else
                tmpParameter = DBHelper.CreateFloatParameter("@Length", ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (!String.IsNullOrEmpty(pole.LVisitor))
                tmpParameter = DBHelper.CreateNVarCharParameter("@LVisitor", 255, ParameterDirection.Input, false, pole.LVisitor);
            else
                tmpParameter = DBHelper.CreateNVarCharParameter("@LVisitor", 255, ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);

            if (!String.IsNullOrEmpty(pole.Number))
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number", 255, ParameterDirection.Input, false, pole.Number);
            else
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number", 255, ParameterDirection.Input, true, DBNull.Value);
            listParameter.Add(tmpParameter);


            listParameter.Add(DBHelper.CreateIntParameter("@poleId", ParameterDirection.Input, false, pole.Id));
            listParameter.Add(DBHelper.CreateIntParameter("@visitFk", ParameterDirection.Input, false, pole.Asset.Id));
            listParameter.Add(DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, pole.SolutionAvailable));


            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameter.ToArray());

        }

        public static int GetNextPoleId(string connectionString)
        {
            StringBuilder _Query;
            _Query = new StringBuilder("Select max(PoleId)  from tb_Vendor_Visit_XStructureMappingPoles ");
            return (Int32)LMobileDatabase.ExecuteScalar(_Query.ToString(), connectionString);
        }

        public static void DeleteMobile(EVendor_Visit_XMissionStructures_Pole pole, string connectionString)
        {
            Dissociate(pole,connectionString);

           StringBuilder _Query = new StringBuilder();
            List<SqlParameter> listParameter = new List<SqlParameter>();
           #region Parameter
            listParameter.Add(DBHelper.CreateIntParameter("@PoleId",ParameterDirection.Input,false,pole.Id));
           #endregion

           #region Query

           _Query.AppendLine("Delete tb_Vendor_Visit_XStructureMappingPoles where PoleId = @PoleId ");

            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameter.ToArray());



        }

        public static void Dissociate(EVendor_Visit_XMissionStructures_Pole pole, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();
            List<SqlParameter> listParameter = new List<SqlParameter>();
            #region Parameter
            listParameter.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));
            #endregion

            #region Query

            _Query.AppendLine("update tb_Vendor_Visit_Images SET PoleFk = null  where PoleFk = @PoleId ");

            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameter.ToArray());



        }

        #endregion

        
    }
}
